package db_layer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model_layer.Sale;
import model_layer.StaffMember;

public class StaffMemberDB
{
	private Connection con;
	public StaffMemberDB()
	{
		con = DBConnection.get_instance().get_connection();
	}
	
	public StaffMember find_staff_member(String var_name, int var, boolean make_association)
	{
		return single_find(var_name, "", var, make_association);
	}
	
	public StaffMember find_staff_member(String var_name, String var, boolean make_association)
	{
		return single_find(var_name, var, -1, make_association);
	}
	
	public List<StaffMember> find_all_staff_members(boolean make_association)
	{
		return multiple_find("", "", -1, make_association);
	}
	
	public List<StaffMember> find_particular_staff_members(String var_name, int var, boolean make_association)
	{
		return multiple_find(var_name, "", var, make_association);
	}
	
	public boolean insert_staff_member(StaffMember sm)
	{
		int rc = -1;
		String lname = sm.getLname();
		String fname = sm.getFname();
		String phone_nr = sm.getPhone_nr();
		String email = sm.getEmail();
		String password = sm.getPassword();
		int privilage_level = sm.getPrivilage_level();
		String username = sm.getLogin();
		int deleted = (sm.isDeleted()) ? 1 : 0;
		
		try
		{
			PreparedStatement ps = DBUtilities.make_insert_statement(con, 
					"StaffMembers", "fname, lname, phone_nr, email, username, pass, privilage_level, deleted");
			ps.setString(1, fname);
			ps.setString(2, lname);
			ps.setString(3, phone_nr);
			ps.setString(4, email);
			ps.setString(5, username);
			ps.setString(6, password);
			ps.setInt(7, privilage_level);
			ps.setInt(8, deleted);
			
			ps.setQueryTimeout(5);
			rc = ps.executeUpdate();
		}
		catch(SQLException e)
		{
			System.err.println("Error occured while inserting staff member: " + e);
		}
		return rc == 1;
	}
	
	public boolean update_staff_member(StaffMember sm)
	{
		int rc = -1;
		int id = sm.getId();
		String lname = sm.getLname();
		String fname = sm.getFname();
		String phone_nr = sm.getPhone_nr();
		String email = sm.getEmail();
		String password = sm.getPassword();
		int privilage_level = sm.getPrivilage_level();
		String username = sm.getLogin();
		int deleted = (sm.isDeleted()) ? 1 : 0;
		
		try
		{
			PreparedStatement ps = DBUtilities.make_update_statement(con, 
					"StaffMembers", "fname, lname, phone_nr, email, username, pass, privilage_level, deleted", "id");
			ps.setString(1, fname);
			ps.setString(2, lname);
			ps.setString(3, phone_nr);
			ps.setString(4, email);
			ps.setString(5, username);
			ps.setString(6, password);
			ps.setInt(7, privilage_level);
			ps.setInt(8, deleted);
			ps.setInt(9, id);
			
			ps.setQueryTimeout(5);
			rc = ps.executeUpdate();
		}
		catch(SQLException e)
		{
			System.err.println("Error while updating staff member: " + e);
		}
		return rc == 1;
		
	}
	
	private StaffMember single_find(String var_name, String s_var, int i_var, boolean make_association)
	{
		String query = make_staff_member_query();
		ResultSet results;
		StaffMember sm = null;
		try
		{
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			if(results.next())
			{
				sm = create_staff_member(results);
				ps.close();
				if(make_association)
				{
					SaleDB sale_db = new SaleDB();
					List<Sale> sales = sale_db.find_particular_sales("sm_id", sm.getId(), false);
					sm.setMade_sales(sales);
				}
			}
		}
		catch(SQLException e)
		{
			System.err.println("Error while finding a staff member: " + e);
		}
		return sm;
	}
	
	private List<StaffMember> multiple_find(String var_name, String s_var, int i_var, boolean make_association)
	{
		List<StaffMember> staff_members = new ArrayList<StaffMember>();
		String query = make_staff_member_query();
		ResultSet results;
		StaffMember sm = null;
		try
		{
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			while(results.next())
			{
				sm = create_staff_member(results);
				if(make_association)
				{
					SaleDB sale_db = new SaleDB();
					List<Sale> sales = sale_db.find_particular_sales("sm_id", sm.getId(), false);
					sm.setMade_sales(sales);
				}
				staff_members.add(sm);
			}
			ps.close();
		}
		catch(SQLException e)
		{
			staff_members.clear();
			System.err.println("Error while finding staff members: " + e);
		}
		return staff_members;
	}

	
	private String make_staff_member_query()
	{
		return DBUtilities.make_query("id, fname, lname, email, phone_nr, privilage_level, username, pass, deleted", "StaffMembers");
	}
	
	private StaffMember create_staff_member(ResultSet results)
	{
		try
		{
			StaffMember sm = new StaffMember();
			sm.setId(results.getInt("id"));
			sm.setFname(results.getString("fname"));
			sm.setLname(results.getString("lname"));
			sm.setEmail(results.getString("email"));
			sm.setPhone_nr(results.getString("phone_nr"));
			sm.setPrivilage_level(results.getInt("privilage_level"));
			sm.setLogin(results.getString("username"));
			sm.setPassword(results.getString("pass"));
			sm.setDeleted(results.getInt("deleted") == 1);
			return sm;
			
		}
		catch(SQLException e)
		{
			System.err.println("Error while creating staff member: " + e);
			return null;
		}
	}
	
	public void check_if_any_account_exist()
	{
		ResultSet results;
		try
		{
			DBConnection.begin_transaction();
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery("SELECT * FROM StaffMembers WHERE privilage_level = 2");
			if(!results.next())
			{
				Statement insert_stmt = con.createStatement();
				insert_stmt.setQueryTimeout(5);
				insert_stmt.executeUpdate("INSERT INTO StaffMembers(phone_nr, email, fname, lname, privilage_level, deleted, username, pass) "
						+ "VALUES ('', '', '', '', 2, 0, 'Owner', '8E3D24EF9E4C5B9867504C13A1C13A6AD7C992748B271151C2F090C66B6FCCD7|c97e4')");
				insert_stmt.close();
			}
			stmt.close();
			DBConnection.end_transaction();

		}
		catch(SQLException e)
		{
			System.err.println("Error while checking if any administrator account exists: " + e);
			DBConnection.rollback_transaction();
		}
	}

}
